﻿using System.Data.SqlClient;

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace CNTesting
{
    public partial class Form2 : Form
    {
        public Form2()
        {
            InitializeComponent();
        }
        

        void Clear()
        {
            txtName.Clear();
            cboSex.Text = "";
            txtAdr.Clear();
            txtEmail.Clear();
            chStatus.Checked = false;        
        }

        //==============================================================
        public SqlConnection cn;
        void Connection()
        {
            cn = new SqlConnection("server=.; uid=sa; pwd=123; database=LOGINDB");
            cn.Open();
        }
        
        void refreshdata()
        {
            Connection(); 
            SqlCommand cmd = new SqlCommand("select * from Testing_tbl where C_Name+Sex+Adr like '%"+ txtSearch.Text +"%';", cn);
            try
            {
                SqlDataAdapter dt = new SqlDataAdapter();
                dt.SelectCommand = cmd;
                DataTable dbdataset = new DataTable();
                dt.Fill(dbdataset);

                //this.dataGridView1.DataSource = AutoNumberedTable(dt);
                //label7.Text = dt.Rows.Count.ToString();

                BindingSource bs = new BindingSource();
                bs.DataSource = dbdataset;
                dataGridView1.DataSource = bs;
                dt.Update(dbdataset);



            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        
        }

        private object AutoNumberedTable(SqlDataAdapter dt)
        {
            throw new NotImplementedException();
        }

        //=============================================================================================

        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {

        }
        //===================================================================================
        private void button1_Click(object sender, EventArgs e)
        {
            try {

             Connection();
             SqlCommand cmd = new SqlCommand("insert into Testing_tbl values (@C_Name,@Sex,@Adr,@Email,@Status)", cn);

            cmd.Parameters.Add("@C_Name", SqlDbType.NVarChar).Value = txtName.Text ;
            cmd.Parameters.Add("@Sex", SqlDbType.NChar).Value = cboSex.Text ;
            cmd.Parameters.Add("@Adr", SqlDbType.NVarChar).Value = txtAdr.Text ;
            cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = txtEmail.Text ;
            cmd.Parameters.Add("@Status", SqlDbType.Int).Value = chStatus.Checked ;
            MessageBox.Show("Insert Success");
            cmd.ExecuteNonQuery();
            cn.Close();
            this.Clear(); 
            this.refreshdata();
            btnNew.Visible = true;
              }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
          }


        //=============================================================================================

        private void Form2_Load(object sender, EventArgs e)
        {

            this.refreshdata();
            //this.WindowState = FormWindowState.Maximized;
        }
        //=============================================================================================

        private void txtSearch_TextChanged(object sender, EventArgs e)
        {
            this.refreshdata();
        }

        //=============================================================================================
        private void btnUpdate_Click(object sender, EventArgs e)
        {
          try
            {
             Connection();
                SqlCommand cmd = new SqlCommand("update Testing_tbl set C_Name=@C_Name, Sex=@Sex, Adr=@Adr, Email=@Email, Status=@Status where ID=@ID", cn);
                
                cmd.Parameters.Add("@ID", SqlDbType.Int).Value = txtID.Text;
                cmd.Parameters.Add("@C_Name", SqlDbType.NVarChar).Value = txtName.Text;
                cmd.Parameters.Add("@Sex", SqlDbType.NChar).Value = cboSex.Text;
                cmd.Parameters.Add("@Adr", SqlDbType.NVarChar).Value = txtAdr.Text;
                cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = txtEmail.Text;
                cmd.Parameters.Add("@Status", SqlDbType.Int).Value = chStatus.Checked;
                MessageBox.Show("Insert Success");
                cmd.ExecuteNonQuery();
                cn.Close();
                this.Clear();
                this.refreshdata();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }
        //=============================================================================================

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
          try
          {
              if (dataGridView1.RowCount>0)
             {
            int i =dataGridView1.CurrentCell.RowIndex ;
            txtID.Text = dataGridView1.Rows[i].Cells["ID"].Value.ToString();
            txtName.Text = dataGridView1.Rows[i].Cells["C_Name"].Value.ToString();
            cboSex.Text = dataGridView1.Rows[i].Cells["Sex"].Value.ToString();
            txtAdr.Text = dataGridView1.Rows[i].Cells["Adr"].Value.ToString();
            txtEmail.Text = dataGridView1.Rows[i].Cells["Email"].Value.ToString();

             }
            } catch (Exception ex){
                MessageBox.Show(ex.Message);
            }  
        }
        //=============================================================================================

        private void btnDelet_Click(object sender, EventArgs e)
        {
            Connection();
            SqlCommand cmd = new SqlCommand("delete from Testing_tbl where ID=@ID;", cn);
            cmd.Parameters.Add ("@ID", SqlDbType.Int).Value = dataGridView1.CurrentRow.Cells["ID"].Value;
            cmd.ExecuteNonQuery();
            this.refreshdata();
            MessageBox.Show("Delete Success");
        }

        private void txtName_TextChanged(object sender, EventArgs e)
        {

        }

        private void btnNew_Click(object sender, EventArgs e)
        {
            txtAdr.Text = "";
            txtEmail.Text = "";
            txtName.Text = "";
            cboSex.Text = "";
            btnNew.Visible = false;
        }
    }
}
